<?php
namespace Heatingfee\Controller;
use Think\Controller;
class AdminController extends Controller {
    //登陆控制器
    public function login(){
        $this -> display();
    }
    //后台首页控制器
    public function index(){
        if(session('name') && session('password')){
            $this -> display();
        }else{
            $this -> redirect('login');
        }

    }
    //验证密码控制器
    public function loginverification(){
        $username = I('post.username');
        $password = I('post.password');
        $qnf_pass = D('qnf_pass');
        $data = $qnf_pass->where("name='{$username}' and passwd='{$password}'")->find();
        if($data){
            session('name',$username);
            session('password',$password);
            $this -> redirect('index');
        }else{
             echo "<script>alert('密码错误');history.go(-1);</script>";
        }
          
    }
       //获取sq文件控制器
    public function sq(){
        $sq = $_GET['sql'];
        if ($sq=='action') { //导出CSV
            $qnf = D('qnf');
            $result = $qnf->select();

            $str = "员工姓名,员工编号,员工部门,取暖费用,年度"."\n";
            $str = iconv('utf-8','gb2312',$str);
            //$str = iconv('utf-8','gb2312',$str);
            foreach($result as $k=>$v){
                // $id = iconv('utf-8','gb2312',$row['Id']); //中文转码
                $name = iconv('utf-8','gb2312',$v['name']); //中文转码
                $userid = iconv('utf-8','gb2312',$v['userid']); //中文转码
                $bm = iconv('utf-8','gb2312',$v['bm']); //中文转码
                $qunuanfei = iconv('utf-8','gb2312',$v['qunuanfei']); //中文转码
                $nf = iconv('utf-8','gb2312',$v['nf']); //中文转码
                $str .= $name.",".$userid.",".$bm.",". $qunuanfei.",". $nf ."\n"; //用引文逗号分开
            }
            $filename = date('YmdHis').'.csv'; //设置文件名
            $this -> export_csv($filename,$str); //导出
        }
    }
       
    //上传excel 文件解析写入数据库
    public function upload(){
        header('Content-type:text/html;charset=utf-8');
        if (! empty ( $_FILES ['file_stu'] ['name'] )){
            $tmp_file = $_FILES ['file_stu'] ['tmp_name'];
            $file_types = explode ( ".", $_FILES ['file_stu'] ['name'] );
            $file_type = $file_types [count ( $file_types ) - 1];
            // echo $file_type;exit;
            /*判别是不是.xls文件，判别是不是excel文件*/
            if ((strtolower($file_type) != "xlsx") && (strtolower($file_type) != "xls") && (strtolower($file_type) != "csv")  ) {
                echo exit("<script>alert('不是Excel文件，重新上传');history.go(-1);</script>");
            }
            /*设置上传路径*/
            $savePath = APP_PATH . 'Heatingfee/excel';
            /*以时间来命名上传的文件*/
            if(!is_dir($savePath)){
                mkdir($savePath,0777,true);
            }
            $str = date ( 'Ymdhis' );
            $file_name = $str . "." . $file_type;
            /*是否上传成功*/
            $newfileexcelname = $savePath.'/'.$file_name;
            if(!move_uploaded_file( $tmp_file,$newfileexcelname)){
                echo exit("<script>alert('文件上传失败');history.go(-1);</script>");
            }else{
                //echo "<script>alert('添加数据库成功');</script>";
            }
            $data = $this -> format_excel2array($newfileexcelname);
            echo '<pre>';
            unset($data[1]);

            if(!array_key_exists('E',$data[2])){
                echo exit("<script>alert('添加失败 请您检查文件是否与指定格式一致');history.go(-1);</script>");
            }
            if(array_key_exists('F',$data[2])){
                echo exit("<script>alert('添加失败 请您检查文件是否与指定格式一致');history.go(-1);</script>");
            }
           // dump($data);die;
            $qnf = D('qnf');
            $qnf->where(1)->delete();
            foreach ($data as $k => $v) {
                $res = $qnf->add(array(
                    'name' => (string)$v['A'],
                    'userid' => (string)$v['B'],
                    'bm' => (string)$v['C'],
                    'qunuanfei' => (int)$v['D'],
                    'nf' => (int)$v['E']
                ));

                if($res){
                    echo '添加数据库成功'.'<br>';
                }else{
                    echo "<script>alert('添加数据库失败');history.go(-1);</script>";
                }
            }
            echo "<script>alert('添加数据库成功');history.go(-1);</script>";
        }
    }
    /**
     * 生成验证码
     */
    public function captcha(){
        $config = array(
            'fontSize' => 30,
            'length' => 4,
            'useNoise' => false
        );
        $Verify = new \Think\Verify($config);$Verify->entry();
    }
    
    /**
     * 校验验证码
     */
    private function check_verify($code, $id = ''){
        $verify = new \Think\Verify();
        return $verify->check($code, $id);
    }
    
    /**
     * ajax检测验证码
     */
    public function ajaxCheckCaptcha(){
        if(!IS_POST){
            $this ->ajaxReturn(0);
        }
        $captcha = I('post.captcha');
        $result = $this -> check_verify($captcha);
        if($result){
            $this ->ajaxReturn(1);
        }else{
            $this ->ajaxReturn(0);
        }
    }
    //解析excel 方法
    public function format_excel2array($filePath='',$sheet=0){
        require APP_PATH.'Heatingfee/Org/Classes/PHPExcel.php';
        if(empty($filePath) or !file_exists($filePath)){die('file not exists');}
        $PHPReader = new \PHPExcel_Reader_Excel2007();        //建立reader对象
        if(!$PHPReader->canRead($filePath)){
            $PHPReader = new \PHPExcel_Reader_Excel5();
            if(!$PHPReader->canRead($filePath)){
                echo exit("<script>alert('不是Excel文件，重新上传');history.go(-1);</script>");
                return ;
            }
        }
        $PHPExcel = $PHPReader->load($filePath);        //建立excel对象
        $currentSheet = $PHPExcel->getSheet($sheet);        //**读取excel文件中的指定工作表*/
        $allColumn = $currentSheet->getHighestColumn();        //**取得最大的列号*/
        $allRow = $currentSheet->getHighestRow();        //**取得一共有多少行*/
        $data = array();
        for($rowIndex=1;$rowIndex<=$allRow;$rowIndex++){        //循环读取每个单元格的内容。注意行从1开始，列从A开始
            for($colIndex='A';$colIndex<=$allColumn;$colIndex++){
                $addr = $colIndex.$rowIndex;
                $cell = $currentSheet->getCell($addr)->getValue();
                if($cell instanceof PHPExcel_RichText){ //富文本转换字符串
                    $cell = $cell->__toString();
                }
                $data[$rowIndex][$colIndex] = $cell;
            }
        }
        return $data;
    }
    //下载sql 文件
    public  function export_csv($filename,$data) {
        header("Content-type:text/csv");
        header("Content-Disposition:attachment;filename=".$filename);
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
        header('Expires:0');
        header('Pragma:public');
        echo $data;
        exit;

    }
}
